package com.example.outdoor;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.Set;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {
	private static final int DB_VERSION = 1;
	private static final String DB_NAME = "OutdoorAppDB.db";
	private static final String TABLE_NAME = "tblAvenue";
	private static final String _id = "id";
	private static final String Avenue = "Avenue";
	private static final String AvenueDescription = "AvenueDescription";
	
	public static ArrayList<String > arr;
	public static ArrayList<String > arra;
	
	public DatabaseHelper(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
	}
	@Override
	public void onCreate(SQLiteDatabase db) {
		String createTableQuery = "create table IF NOT EXISTS " + TABLE_NAME + "(" + _id
				+ " INTEGER PRIMARY KEY," + Avenue + " TEXT,"+AvenueDescription+" TEXT)";
		db.execSQL(createTableQuery);
	}
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

		db.execSQL("drop table if exists " + TABLE_NAME);

		onCreate(db);
	}
	public void insertData(String AvenueValue,String AvenueDescriptionValue ) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(Avenue, AvenueValue);
		values.put(AvenueDescription, AvenueDescriptionValue);
		db.insert(TABLE_NAME, null, values);
		db.close();
	}
	public Set<String> getAllData() {
		Set<String> set = new HashSet<String>();

		String selectQuery = "select * from " + TABLE_NAME;

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		if (cursor.moveToFirst()) {
			do {
				
				//set.add(cursor.getString(1));
				set.add(cursor.getString(2));
			} while (cursor.moveToNext());
		}

		cursor.close();
		db.close();

		return set;
	}
	public Set<String> getAllDataOnFirstTime() {
		Set<String> set = new HashSet<String>();

		String selectQuery = "select * from " + TABLE_NAME+"  LIMIT 1";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		String avenuecode=null;
		if (cursor.moveToFirst()) {
			do {
				//avenuecode=cursor.getString(1);
				
				//set.add(cursor.getString(1));
				//set.add(cursor.getString(2));
			} while (cursor.moveToNext());
		}
	
		avenuecode="5515";
		String selectQuery1 = "select * from tblBillboard  Where Avenue="+avenuecode;

		Cursor cursor1 = db.rawQuery(selectQuery1, null);
			if (cursor1.moveToFirst()) {
				do {
					String startingPoint=null;
					startingPoint=cursor1.getString(28);
					String [] rowData = null;
					rowData=startingPoint.split(" ");
					set.add(rowData[0].toString().replace("\"", ""));
					//arr.add(rowData[0].toString().replace("\"", ""));
					Log.e("set size is", ""+set.size());
					
				} while (cursor1.moveToNext());
			}

			cursor1.close();
			cursor.close();
			db.close();
		
		

		return set;
	}
	public Set<String> getAllDataForStartingPoint(String _AvenueDescription ) {
		Set<String> set = new HashSet<String>();
      String selectQuery1="select * from "+TABLE_NAME+ " Where AvenueDescription='"+ _AvenueDescription+"'";
	
      arr = new ArrayList<String>();
         
     
      
         
         /*for (int i = 0; i < set.size(); i++) {
			
        	 Object[] name = set.toArray().get;
        	 
        	 
        	 
		}*/

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery1, null);
		String AvenueCode = null;
		
		if (cursor.moveToFirst()) {
			do {
				AvenueCode=cursor.getString(1);
				//set.add(cursor.getString(2));
			} while (cursor.moveToNext());
	}
		cursor.close();
	
	String selectQuery = "select * from tblBillboard  Where Avenue="+AvenueCode +" order by Betweenavenue  asc";

	Cursor cursor1 = db.rawQuery(selectQuery, null);
		if (cursor1.moveToFirst()) {
			do {
				String startingPoint=null;
				startingPoint=cursor1.getString(28);
				String [] rowData = null;
				rowData=startingPoint.split(" ");
				set.add(rowData[0].toString().replace("\"", ""));
				arr.add(rowData[0].toString().replace("\"", ""));
				Log.e("array size is", ""+arr.size());
				
			} while (cursor1.moveToNext());
		}

		cursor1.close();
		db.close();

		return set;
	}
	 public Set<String> getAllDataForStartingPointByCode(String paramString)
	  {
		 SQLiteDatabase db = this.getReadableDatabase();
			Set<String> set = new HashSet<String>();
		 String str1 = "select Betweenavenue from tblBillboard  Where Avenue='" + paramString+"'" ;
		 
		 Cursor cursor1 = db.rawQuery(str1, null);
			if (cursor1.moveToFirst()) {
				do {
					String startingPoint=null;
					startingPoint=cursor1.getString(0);
					String [] rowData = null;
					rowData=startingPoint.split(" ");
					set.add(rowData[0].toString().replace("\"", ""));
					//arr.add(rowData[0].toString().replace("\"", ""));
					//Log.e("array size is", ""+arr.size());
					
				} while (cursor1.moveToNext());
			}

			cursor1.close();
			db.close();

			return set;
		 
	 
	  }
}
